﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace com.core.database
{
    class SqlProvider: IDataAgent
    {
        SqlConnection _con = null;
        public SqlProvider(string conStr)
        {
            _con = new SqlConnection(conStr);

        }
        #region IDataAgent Members


        public int ExecuteSP(string sp, DataItem inParams)
        {
            _con.Open();
            SqlCommand command = new SqlCommand(sp, _con);
            command.CommandType = CommandType.StoredProcedure;
            if (inParams == null)
            {
                string[] allParamNames = inParams.Keys;
                foreach (string paramName in allParamNames)
                    command.Parameters.Add(new SqlParameter(paramName, inParams[paramName]));
            }

            SqlParameter returnParam = new SqlParameter("@Return", null);
            returnParam.Direction = ParameterDirection.ReturnValue;
            
            int rowEffects = command.ExecuteNonQuery();
            int retValue = (int)command.Parameters["@Return"].Value;
            _con.Close();
            return retValue;
        }

        public DataItem[] ExecuteSP(string sp, DataItem inParams, DataItem outParams)
        {
            _con.Open();
            SqlCommand command = new SqlCommand(sp, _con);
            command.CommandType = CommandType.StoredProcedure;
            if (inParams == null)
            {
                string[] allParamNames = inParams.Keys;
                foreach (string paramName in allParamNames)
                    command.Parameters.Add(new SqlParameter(paramName, inParams[paramName]));
            }
            if (outParams == null)
            {
                string[] allParamNames = outParams.Keys;
                foreach (string paramName in allParamNames)
                {
                    SqlParameter para = new SqlParameter(paramName, outParams[paramName]);
                    para.Direction = ParameterDirection.InputOutput;
                    command.Parameters.Add(para);
                }
            }
            
            IDataReader reader = command.ExecuteReader();
            
            //get data
            List<DataItem> items = new List<DataItem>();
            while (reader.Read())
            {
                items.Add(CreateDataItems(reader));
            }
            //get output param
            if (outParams == null)
            {
                string[] allParamNames = outParams.Keys;
                foreach (string paramName in allParamNames)
                {
                    outParams[paramName] = command.Parameters[paramName].Value;
                }
            }
            _con.Close();
            if (items.Count > 0) return items.ToArray();
            else return null;
            
        }


        public int ExecuteNonQuery(string query)
        {
            _con.Open();
            SqlCommand command = new SqlCommand(query, _con);
            command.CommandType = CommandType.Text;
            int rowEffects = command.ExecuteNonQuery();
            _con.Close();
            return rowEffects;
        }

        public DataItem[] ExecuteQuery(string query)
        {
            
            _con.Open();
            SqlCommand command = new SqlCommand(query, _con);
            command.CommandType = CommandType.Text;
            IDataReader reader = command.ExecuteReader();
            List<DataItem> items = new List<DataItem>();
            while (reader.Read())
            {
                items.Add(CreateDataItems(reader));
            }
            _con.Close();
            if (items.Count > 0) return items.ToArray();
            else return null;
        }

        #endregion
        DataItem CreateDataItems(IDataReader reader)
        {
            int cols = reader.FieldCount;
            DataItem item = new DataItem();
            for (int i = 0; i < cols; i++)
            {
                item[reader.GetName(i)] = reader[i];
            }
            return item;
        }
    }
}
